package com.gift.common;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {


    /**
     * title:表头内容
     * headMap：列头内容
     * dataList：数据列的List
     * datePattern：数据参数之一《时间格式》，为空及默认yyyy-MM-dd HH:mm:ss
     * colWidth：列宽，过小及默认
     * out：io输出
     * @param title
     * @param headMap
     * @param dataList
     * @param datePattern
     * @param colWidth
     * @param out
     *
     * import com.fasterxml.jackson.databind.JsonNode;
        import com.fasterxml.jackson.databind.ObjectMapper;
     * String jos = JsonMapper.obj2String(obj);
    JsonNode jo = null;
    try {
    jo = new ObjectMapper().readTree(jos);
    } catch (IOException e) {
    e.printStackTrace();
    }

    SXSSFRow dataRow = sheet.createRow(rowIndex);
    for (int i = 0; i < properties.length; i++)
    {
    SXSSFCell newCell = dataRow.createCell(i);
    //list中对象遍历属性，o是属性
    Object o = jo.get(properties[i]);
    String cellValue ;
    if(o==null) {
    cellValue = "";
    }
     */
    public static void exportExcelX(String title, Map<String, String> headMap, List dataList, String datePattern, int colWidth, OutputStream out) throws InvocationTargetException, IllegalAccessException {
        if (datePattern == null) datePattern = "yyyy-MM-dd HH:mm:ss";
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBold(false);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < sheet.getDefaultColumnWidth() ? sheet.getDefaultColumnWidth() : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        Iterator<String> iter = headMap.keySet().iterator();
        while (iter.hasNext()) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : dataList) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }

            Field[] fields = obj.getClass().getDeclaredFields();

            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                SXSSFCell newCell = dataRow.createCell(i);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                Class tCls = obj.getClass();
                Method getMethod = null;
                try {
                    getMethod = tCls.getMethod(getMethodName, new Class[]{});
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                }

                Object value = getMethod.invoke(obj, new Object[]{});

                //判断值的类型后进行强制类型转换
                String textValue = "";
                if(value instanceof LocalDateTime){
                    textValue = DateTimeFormatter.ofPattern(datePattern).format((LocalDateTime)value);
                }else if(value == null){
                    textValue = "";
                }
                else{
                    textValue = value.toString().replace("\"","");
                }
                //数字展示
                Pattern p = Pattern.compile("^(\\-|\\+)?\\d+(\\.\\d+)?$");
                Matcher matcher = p.matcher(textValue);
                if (matcher.matches()) {
                    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                } else {
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                }
                newCell.setCellStyle(cellStyle);
                newCell.setCellValue(textValue);
            }
            rowIndex++;
        }
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void downloadExcelFile(String title,Map<String,String> headMap,List ja,HttpServletResponse response){
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelX(title,headMap,ja,null,29,os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "ISO8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }


}
